﻿using SharpDB.Entity;
using SharpDB.ManagedOracle;
using SharpDB.SPI;
using SharpDB.Utils;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Dynamic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace SharpDB.ManagedOracle
{
    public partial class OracleDB : IDBEntity
    {

        #region IDBEntity 成员

        #region 插入
        public bool Insert<T>(object obj, params string[] excludeColNames)
        {
            return Insert(obj, typeof(T).Name, excludeColNames);
        }

        public bool Insert(object obj, string tableName, params string[] excludeColNames)
        {
            if (obj == null)
                throw new ArgumentException("obj", "不能为null!");
            CheckTabStuct(tableName);
            StringBuilder sb_beforeSQL = new StringBuilder();
            sb_beforeSQL.Append("insert into " + tableName + " (");
            StringBuilder sb_afterSQl = new StringBuilder();
            sb_afterSQl.Append(") values (");

            string insert_sql = string.Empty;
            OracleParam[] paras = null;
            List<OracleParam> lstParam = new List<OracleParam>();

            List<string> lstAllColName = Info[tableName];
            Hashtable ht = ConvertHashTable(obj, lstAllColName, excludeColNames);
            if (ht.Count == 0)
            {
                return false;
            }
            foreach (DictionaryEntry entry in ht)
            {
                string name = entry.Key.ToString();
                string parameterName = ":" + name;
                object objvalue = entry.Value;

                var para = new OracleParam();
                para.ParameterName = parameterName; 
                               
                ColumnInfo cInfo = Info[tableName, name];                
                var kvDbType = GetValueDbType(cInfo, objvalue);
                para.Value = kvDbType.Key;
                para.DbType = kvDbType.Value;

                sb_beforeSQL.Append(name + ",");
                sb_afterSQl.Append(parameterName + ",");

                lstParam.Add(para);
            }
            insert_sql = sb_beforeSQL.ToString().TrimEnd(',') + sb_afterSQl.ToString().TrimEnd(',') + ")";
            paras = lstParam.ToArray();
            int res = ExecSql(insert_sql, paras);
            return res > 0 ? true : false;
        }
        #endregion

        #region 更新
        public bool Update<T>(object obj, string pkOrUniqueColName = "Id", params string[] excludeColNames)
        {
            string tableName = typeof(T).Name;
            return Update(obj, tableName, pkOrUniqueColName, excludeColNames);
        }

        public bool Update(object obj, string tableName, string pkOrUniqueColName = "Id", params string[] excludeColNames)
        {
            CheckTabStuct(tableName, pkOrUniqueColName);
            List<string> lstAllColName = Info[tableName];

            Hashtable ht = ConvertHashTable(obj, lstAllColName, excludeColNames);

            StringBuilder sb_beforeSQL = new StringBuilder();
            sb_beforeSQL.Append("update " + tableName + " set ");

            StringBuilder sb_afterSQl = new StringBuilder();
            sb_afterSQl.Append(" where " + pkOrUniqueColName + "=:" + pkOrUniqueColName);

            string update_sql = string.Empty;
            OracleParam[] paras = null;
            List<OracleParam> lstParam = new List<OracleParam>();
            foreach (DictionaryEntry entry in ht)
            {
                string name = entry.Key.ToString();
                string parameterName = ":" + name;
                object objvalue = entry.Value;

                if (!name.Equals(pkOrUniqueColName, StringComparison.OrdinalIgnoreCase))
                {
                    sb_beforeSQL.Append(name + "=" + parameterName + ",");
                }
                else
                {
                    continue;
                }
                var para = new OracleParam();
                para.ParameterName = parameterName;               
                ColumnInfo cInfo = Info[tableName, name];
                var kvDbType = GetValueDbType(cInfo, objvalue);
                para.Value = kvDbType.Key;
                para.DbType = kvDbType.Value;
                lstParam.Add(para);
            }
            update_sql = sb_beforeSQL.ToString().TrimEnd(',');
            update_sql += sb_afterSQl.ToString();
            lstParam.Add(new OracleParam(":" + pkOrUniqueColName, (object)ht[pkOrUniqueColName]));
            paras = lstParam.ToArray();
            int res = ExecSql(update_sql, paras);
            return res > 0 ? true : false;
        }
        #endregion

        #region 存在则更新，不存在则插入
        public bool Upsert<T>(object obj, string pkOrUniqueColName = "Id", params string[] excludeColNames)
        {
            string tableName = typeof(T).Name;
            return Upsert(obj, tableName, pkOrUniqueColName, excludeColNames);
        }

        public bool Upsert(object obj, string tableName, string pkOrUniqueColName = "Id", params string[] excludeColNames)
        {
            if (obj == null)
            {
                throw new ArgumentException("obj", "不能为null!");
            }
            CheckTabStuct(tableName, pkOrUniqueColName);
            List<string> lstAllColName = Info[tableName];
            List<OracleParam> lstParam = new List<OracleParam>();
            Hashtable ht = ConvertHashTable(obj, lstAllColName, excludeColNames);
            if (ht.Count <= 0)
            {
                return false;
            }

            object pkOrUniqueValue = ht[pkOrUniqueColName];
            if (pkOrUniqueValue == null || string.IsNullOrWhiteSpace(pkOrUniqueValue.ToString())) // 自增主键时，主键值应该为 null 。
            {
                return Insert(obj, tableName, excludeColNames);
            }
            string exist_sql = "select count(1) from " + tableName + " where " + pkOrUniqueColName + "=:" + pkOrUniqueColName;
            var para = new OracleParam();
            para.ParameterName = (":" + pkOrUniqueColName);
            para.Value = pkOrUniqueValue;

            ColumnInfo cInfo = Info[tableName, pkOrUniqueColName];
            var kvDbType = GetValueDbType(cInfo, pkOrUniqueValue);
            para.Value = kvDbType.Key;
            para.DbType = kvDbType.Value;

            lstParam.Add(para);
            if (Exists(exist_sql, lstParam.ToArray()))
            {
                return Update(obj, tableName, pkOrUniqueColName, excludeColNames);
            }
            else
            {
                return Insert(obj, tableName, excludeColNames);
            }
        }
        #endregion

        #region 更新 表的 单个值

        public bool UpSingle<T>(string columnName, object columnValue, object pkOrUniqueValue, string pkOrUniqueColName = "Id")
        {
            string tableName = typeof(T).Name;
            return UpSingle(tableName, columnName, columnValue, pkOrUniqueValue, pkOrUniqueColName);
        }

        public bool UpSingle(string tableName, string columnName, object columnValue, object pkOrUniqueValue, string pkOrUniqueColName = "Id")
        {
            if (pkOrUniqueValue == null)
                throw new ArgumentNullException("pkOrUniqueValue", "不能为null！");
            CheckTabStuct(tableName, columnName, pkOrUniqueColName);
            List<string> lstAllColName = Info[tableName];

            Hashtable ht = new Hashtable();
            ht.Add(columnName, columnValue);
            ht.Add(pkOrUniqueColName, pkOrUniqueValue);
           
            StringBuilder sb_beforeSQL = new StringBuilder();
            sb_beforeSQL.Append("update " + tableName + " set ");

            StringBuilder sb_afterSQl = new StringBuilder();
            sb_afterSQl.Append(" where " + pkOrUniqueColName + "=:" + (pkOrUniqueColName));

            string update_sql = string.Empty;
            OracleParam[] paras = null;
            List<OracleParam> lstParam = new List<OracleParam>();
            foreach (DictionaryEntry entry in ht)
            {
                string name = entry.Key.ToString();
                string parameterName = ":" + name;
                object objvalue = entry.Value;

                if (!name.Equals(pkOrUniqueColName, StringComparison.OrdinalIgnoreCase))
                {
                    sb_beforeSQL.Append(name + "=" + parameterName + ",");
                }
                var para = new OracleParam();
                para.ParameterName = parameterName;
                ColumnInfo cInfo = Info[tableName, name];
                var kvDbType = GetValueDbType(cInfo, objvalue);
                para.Value = kvDbType.Key;
                para.DbType = kvDbType.Value;

                lstParam.Add(para);
            }
            update_sql = sb_beforeSQL.ToString().TrimEnd(',') + sb_afterSQl.ToString();          
            paras = lstParam.ToArray();
            int res = ExecSql(update_sql, paras);
            return res > 0 ? true : false;
        }

        #endregion

        #region 删除

        public int Delete<T>(string columnName, params object[] columnValues)
        {
            string tableName = typeof(T).Name;
            return Delete(tableName, columnName, columnValues);
        }

        public int Delete(string tableName, string columnName, params object[] columnValues)
        {
            CheckTabStuct(tableName, columnName);
            int res = 0;
            if (columnValues != null && columnValues.Length > 0)
            {
                string del_sql = "delete from " + tableName + " where 1=1 " + (Util.SqlIn(columnName, columnValues)) + "";
                res = ExecSql(del_sql);
            }
            return res;
        }


        #endregion

        #region 查询 分页数据获取

        public DataTable GetDataTableByPager(int currentPage, int pageSize, string selColumns, string joinTableName, string orderbyStr)
        {
            return GetDataTableByPager(currentPage, pageSize, selColumns, joinTableName, string.Empty, orderbyStr);
        }

        public DataTable GetDataTableByPager(int currentPage, int pageSize, string selColumns, string joinTableName, string whereStr, string orderbyStr)
        {
            if (string.IsNullOrEmpty(selColumns))
            {
                selColumns = "*";
            }

            if (currentPage <= 0)
            {
                currentPage = 1;
            }

            if (pageSize <= 0)
            {
                pageSize = 50;
            }

            string cntSQL = string.Empty, strPageSQL = string.Empty;
            DataTable data = new DataTable();

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                whereStr = Regex.Replace(whereStr, @"(\s)*(where)?(\s)*(.+)", "and $3$4", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            }

            if (!string.IsNullOrWhiteSpace(orderbyStr))
            {
                orderbyStr = Regex.Replace(orderbyStr, @"(\s)*(order)(\s)+(by)(.+)", "$5", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            }
            else
            {
                throw new ArgumentNullException("orderbyStr");
            }

            string strSQL = "select {0} from {1} where 1=1 {2} order by {3}";
            strSQL = string.Format(strSQL, selColumns, joinTableName, whereStr, orderbyStr);

            strPageSQL = string.Format(@"SELECT * FROM (SELECT A.*, ROWNUM RN FROM ({0}) A)
                                    WHERE RN BETWEEN {1} AND {2}",
                                       strSQL, (currentPage - 1) * pageSize + 1, (currentPage) * pageSize);
            data = QryTable(strPageSQL);
            return data;
        }

        public DataTable GetDataTableByPager(int currentPage, int pageSize, string selColumns, string joinTableName, string whereStr, string orderbyStr, out int totalCount)
        {
            if (string.IsNullOrEmpty(selColumns))
            {
                selColumns = "*";
            }

            if (currentPage <= 0)
            {
                currentPage = 1;
            }

            if (pageSize <= 0)
            {
                pageSize = 50;
            }

            string cntSQL = string.Empty, strPageSQL = string.Empty;
            DataTable data = new DataTable();
            totalCount = 0;

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                whereStr = Regex.Replace(whereStr, @"(\s)*(where)?(\s)*(.+)", "and $3$4", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            }

            if (!string.IsNullOrWhiteSpace(orderbyStr))
            {
                orderbyStr = Regex.Replace(orderbyStr, @"(\s)*(order)(\s)+(by)(.+)", "$5", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            }
            else
            {
                throw new ArgumentNullException("orderbyStr");
            }

            cntSQL = "select count(1) from {0} where 1=1 {1}";
            cntSQL = string.Format(cntSQL, joinTableName, whereStr);

            string strSQL = "select {0} from {1} where 1=1 {2} order by {3}";
            strSQL = string.Format(strSQL, selColumns, joinTableName, whereStr, orderbyStr);


            strPageSQL = string.Format(@"SELECT * FROM (SELECT A.*, ROWNUM RN FROM ({0}) A)
                                    WHERE RN BETWEEN {1} AND {2}",
                                       strSQL, (currentPage - 1) * pageSize + 1, (currentPage) * pageSize);
            data = QryTable(strPageSQL);
            totalCount = QrySingle<int>(cntSQL);

            return data;
        }

        #endregion

        #region 其他查询

        public TReturn QrySingleById<T, TReturn>(string retColumnName, object Idvalue, string pkOrUniqueColName = "Id")
        {
            string tableName = typeof(T).Name;
            CheckTabStuct(tableName, retColumnName, pkOrUniqueColName);
            string single_sql = "select {0} from {1} where 1=1 and " + (pkOrUniqueColName) + "=:" + pkOrUniqueColName;
            single_sql = string.Format(single_sql, retColumnName, tableName);

            var para = new OracleParam();
            para.ParameterName = (":" + pkOrUniqueColName);
            para.Value = Idvalue;

            ColumnInfo cInfo = Info[tableName, retColumnName];
            var kvDbType = GetValueDbType(cInfo, Idvalue);
            para.Value = kvDbType.Key;
            para.DbType = kvDbType.Value;
            return QrySingle<TReturn>(single_sql, para);
        }

        public TReturn QrySingleById<TReturn>(string retColumnName, string tableName, object Idvalue, string pkOrUniqueColName = "Id")
        {
            CheckTabStuct(tableName, retColumnName, pkOrUniqueColName);
            string single_sql = "select {0} from {1} where 1=1 and " + (pkOrUniqueColName) + "=:" + pkOrUniqueColName;
            single_sql = string.Format(single_sql, retColumnName, tableName);

            var para = new OracleParam();
            para.ParameterName = (":" + pkOrUniqueColName);
            para.Value = Idvalue;
            ColumnInfo cInfo = Info[tableName, retColumnName];
            var kvDbType = GetValueDbType(cInfo, Idvalue);
            para.Value = kvDbType.Key;
            para.DbType = kvDbType.Value;
            return QrySingle<TReturn>(single_sql, para);
        }

        public string GetPinJieSingleById(string columnNames, string tableName, object Idvalue, string pkOrUniqueColName = "Id")
        {
            string single_sql = "select {0} from {1} where 1=1 and " + pkOrUniqueColName + "='" + Idvalue + "'";
            string strIsNullCol = columnNames;
            if (columnNames.IndexOf(",") > -1)
            {
                strIsNullCol = string.Join("||", columnNames.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(t => "Nvl(" + t + ",'')"));
            }
            single_sql = string.Format(single_sql, strIsNullCol, tableName);
            return QrySingle<string>(single_sql);
        }


        public bool ExistByColVal(string tableName, string columnName, object columnValue, params object[] excludeValues)
        {
            CheckTabStuct(tableName, columnName);
            string exist_sql = "select count(1) from " + tableName + " where " + columnName + "='" + columnValue + "' ";
            if (excludeValues != null && excludeValues.Length > 0)
            {
                string in_sql = Util.SqlIn(columnName, excludeValues, true);
                exist_sql += in_sql;
            }
            return Exists(exist_sql);
        }

        /// <summary>
        /// 对于联合主键 处理：多列值拼接后 在数据库中是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="currPinJieVal"></param>
        /// <param name="excludePinJieVal"></param>
        /// <param name="columnNames"></param>
        /// <returns></returns>
        public bool ExistByColValByPinJie(string tableName, string currPinJieVal, string excludePinJieVal = null, params string[] columnNames)
        {
            List<string> lstIsNullCol = new List<string>();
            foreach (var columnName in columnNames)
            {
                CheckTabStuct(tableName, columnName);
                lstIsNullCol.Add("nvl(" + columnName + ",'')");
            }

            if (lstIsNullCol != null && lstIsNullCol.Count > 0)
            {
                string strPinJieIsNullCol = string.Join("||", lstIsNullCol);
                string exist_sql = "Select Count(" + strPinJieIsNullCol + ") From " + tableName + " where (" + strPinJieIsNullCol + ")='" + currPinJieVal + "' ";
                if (!string.IsNullOrWhiteSpace(excludePinJieVal))
                {
                    exist_sql += " and (" + strPinJieIsNullCol + ") !='" + excludePinJieVal + "' ";
                }
                return Exists(exist_sql);
            }
            else
            {
                throw new ArgumentException("参数错误！", "columnNames");
            }
        }

        public T GetEntity<T>(object IdValue, string pkOrUniqueColName = "Id")
        {
            string tableName = typeof(T).Name;
            CheckTabStuct(tableName);
            string sel_sql = "select * from " + tableName + " where 1=1 and " + pkOrUniqueColName + "='" + IdValue + "'";
            return QryRow(sel_sql).ConvertToObjectFromDR<T>();
        }

        public List<T> GetList<T>(string whereStr, string orderByStr = "")
        {
            string tableName = typeof(T).Name;
            CheckTabStuct(tableName);
            string sel_sql = "select * from " + tableName + " where 1=1 " + whereStr + " " + (string.IsNullOrWhiteSpace(orderByStr) ? "" : " order by" + orderByStr);
            return QryTable(sel_sql).ConvertToListObject<T>();
        }


        public List<string> GetListColumn<T>(string columnName, string andWhereStr, bool isDistinct = false)
        {
            string tableName = typeof(T).Name;
            CheckTabStuct(tableName);
            string sel_sql = "select " + (isDistinct ? "distinct" : "") + columnName + " from " + tableName + " where 1=1 " + andWhereStr;
            return QryTable(sel_sql).GetFirstCol<string>();
        }

        public List<KeyValuePair<string, string>> GetListKeyValue<T>(string colKeyName, string colValName, string joinWhere = "")
        {
            string tableName = typeof(T).Name;
            CheckTabStuct(tableName);
            string sel_sel = "select distinct a." + colKeyName + " as text,a." + colValName + " as id" + " from " + tableName + " a " + joinWhere;
            return QryTable(sel_sel).ConvertToListObject<KeyValuePair<string, string>>();
        }

        public List<T> GetAll<T>(string orderByStr = "")
        {
            string tableName = typeof(T).Name;
            CheckTabStuct(tableName);
            string sel_sql = "select * from " + tableName + " " + (string.IsNullOrWhiteSpace(orderByStr) ? "" : " order by" + orderByStr);
            return QryTable(sel_sql).ConvertToListObject<T>();
        }

        #endregion

        #region internal
        internal Hashtable ConvertHashTable(object obj, List<string> lstAllColName = null, params string[] excludeColNames)
        {
            Hashtable ht = new Hashtable(StringComparer.OrdinalIgnoreCase);
            excludeColNames = excludeColNames ?? new string[] { };
            if (obj is IDictionary || obj is ExpandoObject)
            {
                IDictionary dict = ((IDictionary)obj);
                foreach (DictionaryEntry entry in dict)
                {
                    string name = entry.Key.ToString();
                    if (lstAllColName != null &&
                        lstAllColName.Contains(name, StringComparer.OrdinalIgnoreCase) &&
                        !excludeColNames.Contains(name, StringComparer.OrdinalIgnoreCase)
                        )
                    {
                        ht.Add(name, entry.Value);
                    }
                    else if (lstAllColName == null && !excludeColNames.Contains(name, StringComparer.OrdinalIgnoreCase))
                    {
                        ht.Add(name, entry.Value);
                    }
                }
            }
            else if (obj is NameValueCollection)
            {
                NameValueCollection nvc = obj as NameValueCollection;
                foreach (string key in nvc.AllKeys)
                {
                    string name = key.ToLower();
                    if (lstAllColName != null &&
                        lstAllColName.Contains(name, StringComparer.OrdinalIgnoreCase) &&
                          !excludeColNames.Contains(name, StringComparer.OrdinalIgnoreCase)
                        )
                    {
                        ht.Add(name, nvc[key]);
                    }
                    else if (lstAllColName == null && !excludeColNames.Contains(name, StringComparer.OrdinalIgnoreCase))
                    {
                        ht.Add(name, nvc[key]);
                    }
                }
            }
            else if (obj is DataRow)
            {
                DataRow dr = obj as DataRow;
                DataTable data = dr.Table;
                foreach (DataColumn dc in data.Columns)
                {
                    string name = dc.ColumnName.ToLower();
                    if (lstAllColName != null &&
                        lstAllColName.Contains(name, StringComparer.OrdinalIgnoreCase) &&
                          !excludeColNames.Contains(name, StringComparer.OrdinalIgnoreCase)
                        )
                    {
                        ht.Add(name, dr[dc.ColumnName]);
                    }
                    else if (lstAllColName == null && !excludeColNames.Contains(name, StringComparer.OrdinalIgnoreCase))
                    {
                        ht.Add(name, dr[dc.ColumnName]);
                    }
                }
            }
            else
            {
                Type tyTable = obj.GetType();
                PropertyInfo[] pyInfos = tyTable.GetProperties(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public | BindingFlags.DeclaredOnly);
                foreach (PropertyInfo pInfo in pyInfos)
                {
                    string name = pInfo.Name.ToString();
                    object objvalue = null;

                    if (lstAllColName != null &&
                        lstAllColName.Contains(name, StringComparer.OrdinalIgnoreCase) &&
                            !excludeColNames.Contains(name, StringComparer.OrdinalIgnoreCase)
                        )
                    {
                        objvalue = pInfo.GetValue(obj, null);
                        ht.Add(name, objvalue);
                    }
                    else if (lstAllColName == null && !excludeColNames.Contains(name, StringComparer.OrdinalIgnoreCase))
                    {
                        ht.Add(name, objvalue);
                    }
                }
            }
            return ht;
        }

        internal void CheckTabStuct(string tableName, params string[] columnNames)
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                throw new ArgumentNullException("tableName", "不能为空！");
            }

            if (!Info.TableNames.Contains(tableName, StringComparer.OrdinalIgnoreCase))
            {
                throw new ArgumentException(string.Format("不存在该表！{0}", "[" + tableName + "]"), "tableName:" + tableName);
            }

            if (columnNames != null && columnNames.Length > 0)
            {
                List<string> lstAllColName = Info[tableName];

                foreach (string columnName in columnNames)
                {
                    if (!lstAllColName.Contains(columnName, StringComparer.OrdinalIgnoreCase))
                    {
                        throw new ArgumentException(string.Format("不存在该列！{0}", "[" + tableName + "." + columnName + "]"), "columnName:" + columnName, null);
                    }
                }
            }
        }

        /// <summary>
        /// 获取其对应的  DbType 类型
        /// </summary>
        internal KeyValuePair<object,System.Data.DbType> GetValueDbType(ColumnInfo cInfo, object value)
        {
            //System.Data.DbType 与其它DbType的映射关系：http://blog.sina.com.cn/s/blog_a32831bd0101kz1w.html

            if (value == null && !cInfo.CanNull)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(this, new OnErrorEventArgs(null, null, new ArgumentNullException(cInfo.ColumnName, "值不能为null！")));
                throw new ArgumentNullException(cInfo.ColumnName, "值不能为null！");
            }

            //当前列可以为null，并且是空字符串，插入或更新当前列为 DBNull.Value
            if ((value == null || string.IsNullOrWhiteSpace(value.ToString())) && cInfo.CanNull)
            {
                return new KeyValuePair<object, System.Data.DbType>(DBNull.Value, System.Data.DbType.AnsiString);
            }

            System.Data.DbType retDbType = System.Data.DbType.AnsiString;
            string typeName = cInfo.TypeName.ToLower();

            if (typeName.Contains("char"))
            {
                value = value.ToString();
                int len = 0;
                if (typeName.StartsWith("varchar"))
                {
                    len = value.ToString().DataLength();
                }
                else if (typeName.StartsWith("nvarchar"))
                {
                    len = value.ToString().Length * 2;
                    retDbType = System.Data.DbType.String;
                }
                else if (typeName.StartsWith("char"))
                {
                    len = value.ToString().DataLength();
                    retDbType = System.Data.DbType.AnsiStringFixedLength;
                }
                else if (typeName.StartsWith("nchar"))
                {
                    len = value.ToString().Length * 2;
                    retDbType = System.Data.DbType.StringFixedLength;
                }

                if (cInfo.Length != -1 && len > cInfo.Length)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(this, new OnErrorEventArgs(null, null, new ArgumentNullException(cInfo.ColumnName, cInfo.ColumnName + "字符串长度超出限制！(" + len + ">" + cInfo.Length + ")")));
                    throw new ArgumentNullException(cInfo.ColumnName, cInfo.ColumnName + "字符串长度超出限制！(" + len + ">" + cInfo.Length + ")");
                }
            }
            else if (typeName.Contains("clob")|| typeName.Contains("nclob"))
            {
                value = value.ToString();
                if (typeName.StartsWith("clob"))
                {
                    retDbType = System.Data.DbType.AnsiString;
                }
                else if (typeName.StartsWith("nclob"))
                {
                    retDbType = System.Data.DbType.String;
                }
            }            
            else if (typeName.Contains("date") || typeName.Contains("timestamp"))
            {
                retDbType = System.Data.DbType.DateTime;
                
                if (value is DateTime)
                {
                    return new KeyValuePair<object, DbType>(value, retDbType);
                }
                else
                {
                    string strVal = value.ToString();                  
                    string reg = @"^(\d{2,4})(/|-|\.)(\d{1,2})(/|-|\.)(\d{1,2})\s+(\d{1,2}):(\d{1,2}):(\d{1,2})(\.|:)(\d{3})$";

                    DateTime dtTemp;                   
                    if (DateTime.TryParse(strVal, out dtTemp))
                    {
                        value = dtTemp;
                    }
                    else if (Regex.IsMatch(strVal, reg)
                       && DateTime.TryParse(Regex.Replace(strVal, reg, "$1-$3-$5 $6:$7:$8.$10", RegexOptions.Compiled), out dtTemp))
                    {
                        value = dtTemp;
                    }
                    else
                    {
                        if (this.OnError != null)
                            this.OnError.Invoke(this, new OnErrorEventArgs(null, null, new ArgumentException(cInfo.ColumnName + "转换" + typeName + "时失败！[" + value + "]", "value")));
                        throw new ArgumentException(cInfo.ColumnName + "转换" + typeName + "时失败！[" + value + "]", "value");
                    }
                }
            }
            else if (typeName.Contains("integer") 
                || typeName.Contains("decimal") 
                || typeName.Contains("float") 
                || typeName.Contains("real")
                ||typeName.Contains("number"))
            {
                retDbType = System.Data.DbType.Decimal;
                decimal dleTemp;
                if (decimal.TryParse(value.ToString(), out dleTemp))
                {
                    value = dleTemp;

                    string strVal = value.ToString().Trim();
                    if (cInfo.Length.HasValue && cInfo.Scale.HasValue
                        && Regex.Replace(strVal, @"^-?(\d+)\.\d+$", "$1").Length > (cInfo.Length.Value - cInfo.Scale.Value))
                    {
                        if (this.OnError != null)
                            this.OnError.Invoke(this, new OnErrorEventArgs(null, null, new ArgumentException(cInfo.ColumnName + "插入" + typeName + "时 出现算术溢出错误！[" + value + "]", "value")));
                        throw new ArgumentException(cInfo.ColumnName + "插入" + typeName + "时 出现算术溢出错误！[" + value + "]", "value");
                    }
                }
                else
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(this, new OnErrorEventArgs(null, null, new ArgumentException(cInfo.ColumnName + "转换" + typeName + "时失败！[" + value + "]", "value")));
                    throw new ArgumentException(cInfo.ColumnName + "转换" + typeName + "时失败！[" + value + "]", "value");
                }
            }            
            else if (typeName.Contains("blob") || typeName.Contains("bfile") || value is byte[])
            {
                retDbType = System.Data.DbType.Binary;
            }
            return new KeyValuePair<object, System.Data.DbType>(value, retDbType);

        }

        #endregion

        #endregion







    }
}
